#!/usr/bin/perl
# This script sets up the database for ExMon

use strict;
use DBI;
use Getopt::Long;

my $db_host;
my $db_port;
my $db_user;
my $db_pass;

## Start Configuration ##
GetOptions ("h=s"		=> \$db_host,
			"port=s"	=> \$db_port,
			"u=s"		=> \$db_user,
			"p=s"		=> \$db_pass);
			
my $db = "ExMon";

## End Configuration ##

if(!$db_host) {
	$db_host = "localhost";
}
if(!$db_port) {
	$db_port = "3306";
}
if(!$db_user) {
	$db_user = "root";
}
if(!$db_pass) {
	$db_pass = "";
}

my $drh = DBI->install_driver("mysql");

my $rc = $drh->func('createdb', $db, $db_host, $db_user, $db_pass, 'admin');
if($DBI::errstr) {
	print "\nThere was a problem:\n";
	print $DBI::errstr;
	print "\n";
}

## now that the database is created let's connect and create the tables
my $dbh = DBI->connect("DBI:mysql:$db:$db_host:$db_port","$db_user","$db_pass")
	or die "connecting : $DBI::errstr\n";
	
## Create machines
$dbh->do("CREATE TABLE machines (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			ip_address VARCHAR(15),
			mac_address VARCHAR(17),
			operating_system VARCHAR(255),
			hardware VARCHAR(255),
			host_name VARCHAR(255),
			user VARCHAR(50),
			uptime VARCHAR(50),
			date_added DATETIME,
			last_time_checked DATETIME,
			notes TEXT,
			do_not_scan INT(1) NOT NULL DEFAULT 1
			)");
						
## Create categories
$dbh->do("CREATE TABLE categories (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			name VARCHAR(30),
			description VARCHAR(255)
			)");
			
## Create categories_machines
$dbh->do("CREATE TABLE categories_machines (
			category_id INT,
			machine_id INT
			)");
			
## Create installed_applications
$dbh->do("CREATE TABLE installed_applications (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			machine_id INT,
			name VARCHAR(255)
			)");
			
## Create port_entries
$dbh->do("CREATE TABLE port_entries (
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
			machine_id INT,
			port_number INT,
			service VARCHAR(50),
			state VARCHAR(20),
			last_time_found DATETIME
			)");